package vn.fsoft.gst.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class HSQLDBData {

	// JDBC driver of HSQL database
	private static final String CLASS_NAME = "org.hsqldb.jdbcDriver";

	// URL, user name, password for connection
	private static final String URL = "jdbc:hsqldb:mem:sampledb";
	private static final String USER_NAME = "sa";
	private static final String PASSWORD = "";

	// SQL strings to create table and insert sample data
	private static final String DROP = "DROP SCHEMA PUBLIC CASCADE";
	private static final String ORGANISATIONCREATTABLE = "CREATE TABLE IF NOT EXISTS organisations (OrgID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),AddressID int NOT NULL,OrgName varchar(50) NOT NULL,OrgShortDescription varchar(100) DEFAULT NULL,AddressLine1 varchar(50) DEFAULT NULL,AddressLine2 varchar(50) DEFAULT NULL,AddressLine3 varchar(50) DEFAULT NULL,PreferredOrg bit(1) DEFAULT NULL,PhoneNumber varchar(10) DEFAULT NULL,Fax varchar(10) DEFAULT NULL,WebAddress varchar(10) DEFAULT NULL,CharityNumber int DEFAULT NULL,CompanyNumber int DEFAULT NULL,IsActive bit(1) DEFAULT NULL,contactID int DEFAULT NULL,SICCode char(5) DEFAULT NULL,PRIMARY KEY (OrgID),UNIQUE (OrgName),CONSTRAINT FK_Organisation_sic FOREIGN KEY (SICCode) REFERENCES sic (SICCode) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT FK_Organisation_Address FOREIGN KEY (AddressID) REFERENCES address (AddressID))";
	private static final String TOWNCREATETABLE ="CREATE TABLE IF NOT EXISTS town (TownID INTEGER  NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),CountyID INTEGER  NOT NULL,CountryID INTEGER  NOT NULL,TownName varchar(20) DEFAULT NULL,PRIMARY KEY (TownID),FOREIGN KEY (CountryID) REFERENCES country (CountryID),FOREIGN KEY (CountyID) REFERENCES county (CountyID))/*!40101 SET character_set_client = @saved_cs_client */;";
	private static final String ADDRESSCREATETABLE ="CREATE TABLE IF NOT EXISTS address (AddressID INTEGER  NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),TownID INTEGER  DEFAULT NULL,CountyID INTEGER  DEFAULT NULL,CountryID INTEGER  DEFAULT NULL,PostCode char(5) DEFAULT NULL,IsActive bit(1) DEFAULT NULL,Street varchar(100) DEFAULT NULL,PRIMARY KEY (AddressID),FOREIGN KEY (TownID) REFERENCES town (TownID),FOREIGN KEY (CountryID) REFERENCES country (CountryID),FOREIGN KEY (CountyID) REFERENCES county (CountyID)) /*!40101 SET character_set_client = @saved_cs_client */;";
	private static final String COUNTYCREATETABLE = "CREATE TABLE IF NOT EXISTS county (CountyID INTEGER  NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),CountyName varchar(20) DEFAULT NULL,PRIMARY KEY (CountyID))/*!40101 SET character_set_client = @saved_cs_client */;";
	private static final String COUNTRYCREATETABLE = "CREATE TABLE IF NOT EXISTS country (CountryID INTEGER  NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),CountryName varchar(20) DEFAULT NULL,PRIMARY KEY (CountryID)) /*!40101 SET character_set_client = @saved_cs_client */;";
	private static final String SICCREATETABLE = "CREATE TABLE IF NOT EXISTS sic (SICCode char(5) NOT NULL,TypeOfBussiness varchar(200) DEFAULT NULL,PRIMARY KEY (SICCode))";
	private static final String SUPPORTINGMATERIALCREATETABLE = "CREATE TABLE IF NOT EXISTS supportingmaterial (SupportingMaterialID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),OrgID int NOT NULL,UserID int NOT NULL,URL varchar(500) NOT NULL,Description varchar(500) DEFAULT NULL,Type varchar(45) NOT NULL,AddedDate date NOT NULL,IsActive bit(1) DEFAULT NULL,PRIMARY KEY (SupportingMaterialID),CONSTRAINT FK_Supporting_Material_Organisations FOREIGN KEY (OrgID) REFERENCES organisations (OrgID),CONSTRAINT FK_Supporting_Material_USER FOREIGN KEY (UserID) REFERENCES user (UserID)) ";
	private static final String USERCREATETABLE ="CREATE TABLE IF NOT EXISTS user (UserID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),Account varchar(50) NOT NULL,Email varchar(50) NOT NULL,Role char(10) NOT NULL,Password varchar(50) NOT NULL,PRIMARY KEY (UserID))";
	private static final String SERVICECREATETABLE ="CREATE TABLE IF NOT EXISTS service (ServiceID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),ContactID int NOT NULL,PRIMARY KEY (ServiceID),CONSTRAINT FK_PROGRAME_Service FOREIGN KEY (ContactID) REFERENCES contact (ContactID))";
	private static final String PROGRAMECREATETABLE ="CREATE TABLE IF NOT EXISTS programe (ProgID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),ContactID int NOT NULL,ProgrameName varchar(30) DEFAULT NULL,PRIMARY KEY (ProgID),CONSTRAINT FK_PROGRAME_Contact FOREIGN KEY (ContactID) REFERENCES contact (ContactID))";
	private static final String ORGANISATIONSERVICECREATETABLE ="CREATE TABLE IF NOT EXISTS organisationservice (OrgID int NOT NULL,ServiceID int NOT NULL,PRIMARY KEY (OrgID,ServiceID),CONSTRAINT FK_Organisation_Services1 FOREIGN KEY (OrgID) REFERENCES organisations (OrgID),CONSTRAINT FK_Organisation_Services2 FOREIGN KEY (ServiceID) REFERENCES service (ServiceID))";
	private static final String ORGANISATIONPROGRAMECREATETABLE = "CREATE TABLE IF NOT EXISTS organisationprograme (OrgID int NOT NULL,ProgID int NOT NULL,PRIMARY KEY (OrgID,ProgID),CONSTRAINT FK_Organisation1 FOREIGN KEY (OrgID) REFERENCES organisations (OrgID),CONSTRAINT FK_Programe1 FOREIGN KEY (ProgID) REFERENCES programe (ProgID))";
	private static final String DIRECTORATECREATETABLE = "CREATE TABLE IF NOT EXISTS directorate (DirectorateID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),OrgID int NOT NULL,ContactID int NOT NULL,DirectorateName varchar(200) NOT NULL,DescriptionShort varchar(100) NOT NULL,DescriptionFull varchar(500) NOT NULL,AddressLine1 varchar(100) DEFAULT NULL,AddressLine2 varchar(100) DEFAULT NULL,AddressLine3 varchar(100) DEFAULT NULL,SICCode char(5) DEFAULT NULL,PhoneNumber char(10) DEFAULT NULL,Fax char(10) DEFAULT NULL,Email varchar(100) DEFAULT NULL,WebAddress varchar(100) DEFAULT NULL,CharityNumber varchar(50) DEFAULT NULL,CompanyNumber varchar(50) DEFAULT NULL,AddressID int NOT NULL,IsActive bit(1) DEFAULT NULL,PRIMARY KEY (DirectorateID),CONSTRAINT FK_Directorate_Organisations FOREIGN KEY (OrgID) REFERENCES organisations (OrgID),CONSTRAINT FK_Organisation_Contact FOREIGN KEY (ContactID) REFERENCES contact (ContactID),CONSTRAINT FK_Organisation_Address1 FOREIGN KEY (AddressID) REFERENCES address (AddressID))";
	private static final String DEPARTMENTCREATETABLE ="CREATE TABLE IF NOT EXISTS department (DepartmentID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),DirectorateID int NOT NULL,ContactID int NOT NULL,DepartmentName varchar(100) NOT NULL,IsActive bit(1) DEFAULT NULL,DepartmentShortDes varchar(100) NOT NULL,DepartmentFullDes varchar(1000) DEFAULT NULL,AddressLine1 varchar(100) DEFAULT NULL,AddressLine2 varchar(100) DEFAULT NULL,AddressLine3 varchar(100) DEFAULT NULL,AddressID int NOT NULL,PhoneNumber char(10) DEFAULT NULL,Fax char(10) DEFAULT NULL,Email varchar(50) DEFAULT NULL,WebAddress varchar(50) DEFAULT NULL,SICCode char(5) DEFAULT NULL,PRIMARY KEY (DepartmentID),CONSTRAINT FK_Department_Directorate FOREIGN KEY (DirectorateID) REFERENCES directorate (DirectorateID),CONSTRAINT FK_Department_Address FOREIGN KEY (AddressID) REFERENCES address (AddressID),CONSTRAINT FK_Department_Contact FOREIGN KEY (ContactID) REFERENCES contact (ContactID))";
	private static final String CONTACTCREATETABLE ="CREATE TABLE IF NOT EXISTS contact (ContactID int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),FirstName varchar(20) DEFAULT NULL,Mobile varchar(10) DEFAULT NULL,ContactType varchar(20) DEFAULT NULL,IsActive bit(1) DEFAULT NULL,email varchar(100) DEFAULT NULL,PRIMARY KEY (ContactID))";
	
	private static final String ADDRESSINSERT = "INSERT INTO address (AddressID, TownID, CountyID, CountryID, PostCode, IsActive, Street) VALUES (1,12,1,1,'73000',true,'01 Vo Van Ngan'),(2,12,1,1,'73201',true,'78 Nguyen Van Ba'),(3,12,1,1,'73103',true,'81 Dang Van Bi');";
	private static final String CONTACTINSERT = "INSERT INTO contact (ContactID, FirstName, Mobile, ContactType, IsActive, email) VALUES (1,'Tien','012935494','1',true,'nmt@gmail.com'),(2,'ADFs','366656456','1',true,'nmwet@gmail.com');";
	private static final String COUNTRYINSERT = "INSERT INTO country (CountryID, CountryName) VALUES (1,'Singapore'),(2,'USA'),(3,'Thailand'),(4,'Campudia'),(5,'Lao'),(6,'England');";
	private static final String COUNTYINSERT = "INSERT INTO county (CountyID, CountyName) VALUES (1,'Ha Noi'),(2,'Hai Phong'),(3,'Da Nang'),(4,'Can Tho'),(5,'Hue'),(6,'Quang Nam'),(7,'Quang Ngai'),(8,'Nha Trang'),(9,'Binh Thuan'),(10,'Quy Nhon');";
	private static final String ORGANISATIONINSERT ="INSERT INTO organisations (OrgID, AddressID, OrgName, OrgShortDescription, AddressLine1, AddressLine2, AddressLine3, PreferredOrg, PhoneNumber, Fax, WebAddress, CharityNumber, CompanyNumber, IsActive, contactID, SICCode) VALUES (1,1,'UTE','dsfh sff','asdh khfdf',NULL,NULL,true,NULL,NULL,NULL,NULL,NULL,true,NULL,NULL);";
	@SuppressWarnings("unused")
	private static final String SUPPORTINGMATERIALINSERT = "INSERT INTO supportingmaterial (SupportingMaterialID, OrgID, UserID, URL, Description, Type, AddedDate, IsActive) VALUES (1,1,1,'https://www.facebook.com','Facebook','pdf','2014-08-26',true),(2,1,1,'http://www.tutorialspoint.com/java/java_date_time.htm','Date - Time','doc','2014-08-26',true),(3,1,1,'http://gst.fsoft.com.vn/redmine/issues/5979','Redmine','doc','2014-08-26',true),(4,1,1,'http://www.datatables.net/download/#Git-/-source-control','datatables','pdf','2014-08-26',true),(5,1,1,'http://dantri.com.vn/','Dan tri','doc','2014-08-26',true),(6,1,1,'http://www.thanhnien.com.vn/pages/default.aspx','Thanh nien','doc','2014-08-26',true),(7,1,1,'http://gst.fsoft.com.vn/redmine/issues/5970','http://gst.fsoft.com.vn/redmine/issues/5979','doc','2014-08-26','\0'),(8,1,1,'http://gst.fsoft.com.vn/redmine/issues/597434','http://gst.fsoft.com.vn/redmine/issues/5979sdsd','doc','2014-08-26','\0'),(9,1,1,'http://gst.fsoft.com.vn/redmine/issues/5979sdssaa','http://gst.fsoft.com.vn/redmine/issues/5979fsfff','excel','2014-08-26',true),(10,1,1,'http://gst.fsoft.com.vn/redmine/issues/5979sdadfhhykijk','http://gst.fsoft.com.vn/redmine/issues/5979sffgykhk','pdf','2014-08-26',true),(11,1,1,'http://gst.fsoft.com.vn/redmine/issues/5979uoioghg','http://gst.fsoft.com.vn/redmine/issues/5979guyibnd','doc','2014-08-26',true),(12,1,1,'','http://getbootstrap.com/css/#forms','doc','2014-08-27',true),(13,1,1,'http://1000hz.github.io/bootstrap-validator/','http://1000hz.github.io/bootstrap-validator/','doc','2014-08-27',true),(14,1,1,'http://getbootstrap.com/css/','http://getbootstrap.com/css/','excel','2014-08-27',true);";
	private static final String TOWNINSERT = "INSERT INTO town (TownID, CountyID, CountryID, TownName) VALUES (1,1,1,'Quan 1'),(2,1,1,'Quan 2'),(3,1,1,'Quan 3'),(4,1,1,'Quan 4'),(5,1,1,'Quan 5'),(6,1,1,'Quan 6'),(7,1,1,'Quan 7'),(8,1,1,'Quan 9'),(9,1,1,'Quan 10'),(10,1,1,'Quan 11'),(11,1,1,'Quan 12'),(12,1,1,'Quan Thu Duc'),(13,1,1,'Quan Tan Binh'),(14,1,1,'Quan Binh Thanh'),(15,1,1,'Quan Phu Nhuan'),(16,10,1,'LaGi'),(17,10,1,'Ham Thuan Nam'),(18,10,1,'Ham Thuan Bac'),(19,10,1,'Tanh Linh'),(20,10,1,'Bac Binh'),(21,10,1,'Bac Ruong'),(22,10,1,'Phan Thiet'),(23,10,1,'Duc Linh'),(24,10,1,'Ham Tan'),(25,10,1,'Phu Quy'),(26,10,1,'Tuy Phong'),(27,2,1,'Quan Ba Dinh'),(28,2,1,'Quan Hoan Kiem'),(29,2,1,'Hai Ba Trung'),(30,2,1,'Quan Dong Da'),(31,2,1,'Quan Tay Ho'),(32,2,1,'Quan Cau Giay'),(33,2,1,'Quan Thanh Xuan'),(34,2,1,'Quan Hoang Mai'),(35,2,1,'QUan Long Bien'),(36,2,1,'Tuy Phong'),(37,2,1,'Hyuen Tu Liem'),(38,2,1,'Huyen Gia Lam'),(39,2,1,'Huyen Dong Anh'),(40,4,1,'Quan Hai Chau'),(41,4,1,'Quan Thanh Khe'),(42,4,1,'Quan Son Tra'),(43,4,1,'Quan Ngu Hanh Son'),(44,4,1,'Quan Lien CHieu'),(45,4,1,'Quan Cam Le'),(46,4,1,'Huyen Voa Vang'),(47,8,1,'Huyen Duc Pho'),(48,8,1,'Huyen Ba To'),(49,8,1,'Huyen Duc Pho'),(50,8,1,'Huyen Mo Duc'),(51,8,1,'Huyen Nghia Hanh'),(52,8,1,'Huyen Son Tay'),(53,8,1,'Huyen Son Ha'),(54,8,1,'Huyen Son Tinh'),(55,8,1,'Huyen Tay Tra');";
	private static final String USERINSERT = "INSERT INTO user (UserID, Account, Email, Role, Password) VALUES (1,'admin','admin@admin.com','admin','123456'),(2,'admin1','admin1@admin.com','admin','123456'),(3,'admin2','admin2@admin.com','admin','123456'),(4,'admin3','admin3@admin.com','admin','123456'),(5,'admin4','admin4@admin.com','admin','123456');";
	
	
	
	public HSQLDBData() {
		try {
			Class.forName(CLASS_NAME);
			Connection conn = DriverManager.getConnection(URL, USER_NAME,
					PASSWORD);
			// create tables
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(DROP);
			stmt.executeUpdate(COUNTRYCREATETABLE);
			stmt.executeUpdate(COUNTYCREATETABLE);
			stmt.executeUpdate(TOWNCREATETABLE);
			stmt.executeUpdate(ADDRESSCREATETABLE);
			stmt.executeUpdate(SICCREATETABLE);
			stmt.executeUpdate(ORGANISATIONCREATTABLE);
			stmt.executeUpdate(CONTACTCREATETABLE);
			stmt.executeUpdate(DIRECTORATECREATETABLE);
			stmt.executeUpdate(DEPARTMENTCREATETABLE);
			stmt.executeUpdate(PROGRAMECREATETABLE);
			stmt.executeUpdate(SERVICECREATETABLE);
			stmt.executeUpdate(ORGANISATIONPROGRAMECREATETABLE);
			stmt.executeUpdate(ORGANISATIONSERVICECREATETABLE);
			stmt.executeUpdate(USERCREATETABLE);
			stmt.executeUpdate(SUPPORTINGMATERIALCREATETABLE);
			//insert data
			stmt.executeUpdate(USERINSERT);
			stmt.executeUpdate(COUNTRYINSERT);
			stmt.executeUpdate(COUNTYINSERT);
			stmt.executeUpdate(TOWNINSERT);
			stmt.executeUpdate(ADDRESSINSERT);
			stmt.executeUpdate(ORGANISATIONINSERT);
			stmt.executeUpdate(CONTACTINSERT);
//			stmt.executeUpdate(SUPPORTINGMATERIALINSERT);
		} catch (Exception e) {
			//System.out.print("ERROR");
			e.printStackTrace();
		}
	}
}
